package pilgun.eshop.repository;

import java.util.List;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;


import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.jdbc.core.RowCallbackHandler;

import pilgun.eshop.domain.Cart;
import pilgun.eshop.domain.ItemTuple;
import pilgun.eshop.domain.Order;
import pilgun.eshop.domain.OrderStatus;
import pilgun.eshop.domain.User;

import pilgun.eshop.domain.Item;

public class OrderRepositoryTemplate implements OrderRepository {
	
	private static final String ORDER_SEL_QUERY_ID = "select orderdate, userid from orders where id = ?";
    private static final String ORDERED_SEL_QUERY = "select merchid, stock from ordered where orderid = ?";
    private static final String ORDERSTATUS_SEL_QUERY = "select statusdate, statusplace, status from orderstatuses where orderid = ?";
    private static final String INSERT_ORDER_NOID = "INSERT INTO orders (userid, orderdate) values (?, ?)";
    private static final String GET_LAST_ID = "SELECT MAX(id) from orders";
    private static final String INSERT_ORDERSTATUS = "INSERT INTO orderstatuses (orderid, statusdate, statusplace, status) values (?, ?, ?, ?)";
    
	private JdbcOperations template;
	private UserRepository userRepository;
	private ItemRepository itemRepository;
	
	@Autowired
	public OrderRepositoryTemplate(JdbcOperations template, UserRepository userRepository, ItemRepository itemRepository) {
	this.template = template;
	this.userRepository = userRepository;
	this.itemRepository = itemRepository;
	}
	
	
	    
    public Order getOrderById(final int orderId){
    	final Order anOrder = new Order();
    	final Cart aCart = new Cart();
    	
    	template.query(ORDER_SEL_QUERY_ID, new  RowCallbackHandler(){
    		@Override
            public void processRow(ResultSet rs) throws SQLException {
            	User anUser = userRepository.getUserById(rs.getInt("userid"));
                anOrder.setId(orderId);
                anOrder.setUser(anUser);
                anOrder.setOrderDate(rs.getDate("orderdate"));
            }
        }, 
        orderId);
    	
    	template.query(ORDERED_SEL_QUERY, new  RowCallbackHandler(){
            public void processRow(ResultSet rs) throws SQLException {
            	Item anItem = itemRepository.getItemById(rs.getInt("merchid"));
            	int stock = rs.getInt("stock");
                aCart.addToItemList(anItem, stock);
            }
        }, 
        orderId);	
    	anOrder.setCart(aCart);
    	
    	template.query(ORDERSTATUS_SEL_QUERY, new  RowCallbackHandler(){
    		@Override
            public void processRow(ResultSet rs) throws SQLException {
            	OrderStatus status = new OrderStatus(rs.getDate("statusdate"), rs.getString("statusplace"), rs.getString("status"));
                anOrder.addToStatusList(status);
            }
        }, 
        orderId);	
    	
    	return anOrder;
    }
    
    public int confirmOrder(Cart aCart, User anUser){
    	int cartSize = aCart.getItemList().size();
    	if (cartSize > 0){
    		Date orderDate = new Date();
	    	template.update(INSERT_ORDER_NOID, anUser.getId(), orderDate);
	    	int orderId = template.queryForObject(GET_LAST_ID,  Integer.class);
	    	String insertOrdered = "INSERT INTO ordered (orderid, merchid, stock) values ";
	    	List argList = new ArrayList();
	    	
	    	for (ItemTuple tuple : aCart.getItemList()){
	    		insertOrdered+=("(?, ?, ?),");
	    		argList.add(orderId);
	    		argList.add(tuple.item.getId());
	    		argList.add(tuple.stock);
	    	}
	
	    	insertOrdered = insertOrdered.substring(0, insertOrdered.length()-1);
	    	Object[] argArray = argList.toArray();
	    	template.update(insertOrdered, argArray);
	    	
	    	template.update(INSERT_ORDERSTATUS, orderId, new Date(), "Kyiv", "processing");
	    	return orderId;
    	}else{
    		return -1;
    	}
    }
	
}
